import pymysql


# 封装数据库
class DBTools:
    # 创建类方法 建立数据库连接
    @classmethod
    def __get_conn(cls):
        conn = pymysql.connect(host='211.103.136.244', port=7061, user='student',
                               password='iHRM_student_2021', database='ihrm', charset='utf8')
        return conn

    # 查询一条数据
    @classmethod
    def select_fetchone(cls, sql, rownumber=0):

        conn = None
        cursor = None
        x = None
        try:
            # 调用数据库连接
            conn = cls.__get_conn()
            # 获取游标
            cursor = conn.cursor()
            # 执行sql语句
            cursor.execute(sql)
            # 设置游标位置
            cursor.rownumber = rownumber
            # 处理结果集
            x = cursor.fetchone()
        except Exception as e:
            print(e)

        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            return x

    # 查询多条数据
    @classmethod
    def select_fetchmany(cls, sql, y, rownumber=0):
        conn = None
        cursor = None
        x = None
        try:
            conn = cls.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            # 设置游标位置
            cursor.rownumber = rownumber
            x = cursor.fetchmany(y)
        except Exception as e:
            print(e)

        finally:
            cursor.close()
            conn.close()
            return x

    # 查询全部数据
    @classmethod
    def select_fetchall(cls, sql, rownumber=0):
        conn = None
        cursor = None
        x = None
        try:
            conn = cls.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            # 设置游标位置
            cursor.rownumber = rownumber
            x = cursor.fetchall()
        except Exception as e:
            print(e)

        finally:
            cursor.close()
            conn.close()
            return x

    # 增删改数据
    @classmethod
    def uid_db(cls, sql):
        conn = None
        cursor = None

        try:
            conn = cls.__get_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            # 提交事务
            conn.commit()
            print('事务成功')
            print(conn.affected_rows())
        except Exception as e:
            print(e)
            # 回滚事务
            conn.rollback()
            print('事务回滚')

        finally:
            cursor.close()
            conn.close()


if __name__ == '__main__':
    # 查询
    print(DBTools.select_fetchone('select * from bs_user where id=213'))
    # 添加
    DBTools.uid_db('insert into bs_user(id,title,pub_date) values(120,"俏丽哇","2038-03-03")')
    # 修改
    DBTools.uid_db("update bs_user set title='俏丽哇' where id=275")
    # 删除
    DBTools.uid_db('delete from bs_user where mobile="13877668833"')